/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package wargame.dao;

import wargame.entity.*;
import connectionmanager.DB;
import java.util.ArrayList;

/**
 *
 * @author Clarence
 */
public class CompanyDAO {

    public static Company retrieveCompany(int company_id) {
        String sql = "SELECT `company_id`, `name`, `preferred_skill_dot_net`, `preferred_skill_c_sharp`, `preferred_skill_jsp`, `preferred_skill_php`, `preferred_skill_ruby`, `preferred_skill_mysql`, `preferred_skill_mssql`, `preferred_skill_oracle`, `budget`, `economic_sensitivity` FROM `company` WHERE company_id = '" + company_id + "'";
        DB db = new DB();
        db.connect();
        db.query(sql);
        if (db.next()) {
            Company c = populate(db);
            return c;
        } else {
            return null;
        }
    }

    public static ArrayList<Company> retrieveAll() {
        String sql = "SELECT * FROM company";
        DB db = new DB();
        db.connect();
        db.query(sql);
        ArrayList<Company> company_list = new ArrayList<Company>();
        while (db.next()) {
            company_list.add(populate(db));
        }
        return company_list;
    }

    private static Company populate(DB db) {
        Company c = new Company();
        c.setCompany_id(db.getInt("company_id"));
        c.setName(db.getString("name"));
        c.setPreferred_skill_dot_net(db.getInt("preferred_skill_dot_net"));
        c.setPreferred_skill_c_sharp(db.getInt("preferred_skill_c_sharp"));
        c.setPreferred_skill_jsp(db.getInt("preferred_skill_jsp"));
        c.setPreferred_skill_php(db.getInt("preferred_skill_php"));
        c.setPreferred_skill_ruby(db.getInt("preferred_skill_ruby"));
        c.setPreferred_skill_mysql(db.getInt("preferred_skill_mysql"));
        c.setPreferred_skill_mssql(db.getInt("preferred_skill_mssql"));
        c.setPreferred_skill_oracle(db.getInt("preferred_skill_oracle"));
        c.setBudget(db.getInt("budget"));
        c.setEconomic_sensitivity(db.getDouble("economic_sensitivity"));
        return c;
    }

    public static int insertCompany(Company c) {
        String sql = "INSERT INTO company (`name`, `preferred_skill_dot_net`,"
                + " `preferred_skill_c_sharp`, `preferred_skill_jsp`,"
                + " `preferred_skill_php`, `preferred_skill_ruby`,"
                + " `preferred_skill_mysql`, `preferred_skill_mssql`,"
                + " `preferred_skill_oracle`, `budget`, `economic_sensitivity`) VALUES ('"
                + c.getName() + "', '" + c.getPreferred_skill_dot_net() + "', '"
                + c.getPreferred_skill_c_sharp() + "', '" + c.getPreferred_skill_jsp() + "', '"
                + c.getPreferred_skill_php() + "', '" + c.getPreferred_skill_ruby() + "', '"
                + c.getPreferred_skill_mysql() + "', '" + c.getPreferred_skill_mssql() + "', '"
                + c.getPreferred_skill_oracle() + "', '" + c.getBudget() + "', '"
                + c.getEconomic_sensitivity() + "');";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        int lastInsertedId = db.getLastInsertedID();
        db.close();
        return lastInsertedId;
    }

    public static void updateCompany(Company c) {
        String sql = "UPDATE company SET `name` = '" + c.getName()
                + "', `preferred_skill_dot_net` = '" + c.getPreferred_skill_dot_net()
                + "', `preferred_skill_c_sharp` = '" + c.getPreferred_skill_c_sharp()
                + "', `preferred_skill_jsp` = '" + c.getPreferred_skill_jsp()
                + "', `preferred_skill_php` = '" + c.getPreferred_skill_php()
                + "', `preferred_skill_ruby` = '" + c.getPreferred_skill_ruby()
                + "', `preferred_skill_mysql` = '" + c.getPreferred_skill_mysql()
                + "', `preferred_skill_mssql` = '" + c.getPreferred_skill_mssql()
                + "', `preferred_skill_oracle` = '" + c.getPreferred_skill_oracle()
                + "', `budget` = '" + c.getBudget()
                + "', `economic_sensitivity` = '" + c.getEconomic_sensitivity()
                + "' WHERE company_id = '" + c.getCompany_id() + "';";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }

    public static void clear() {
        String sql = "TRUNCATE TABLE company";
        DB db = new DB();
        db.connect();
        db.execute(sql);
        db.close();
    }
}
